二、综合题

1. 描述（10分）
针对如下表Author结构创建索引，写出创建索引的SQL语句：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

对FirstName创建唯一索引uniq_idx_firstname，对LastName创建普通索引idx_lastname

create unique  index uniq_idx_firstname
    on Author(FirstName)

create index idx_lastname
    on  Author(FirstName)




2. 描述（15分）
构造一个触发器trg_AuditLog，在向Employees表中插入一条数据的时候，触发插入相关的数据到AuditLog中。

-- 职员表
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- 审计日志表
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);

后台会往Employees插入一条数据:
INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
然后从AuditLog里面使用查询语句:
select * from AuditLog;

输出：
Paul | 20000.00



create trigger trg_AuditLog
    on Employees
    for insert
    as
    begin
		insert AuditLog  select Name,SALARY from inserted
    end


3. 描述（10分）
针对Author表创建视图vw_Author，只包含FirstName以及LastName两列，并对这两列重新命名，FirstName为v_FirstName，LastName修改为v_LastName：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
后台会插入2条数据:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

查询视图：select * from vw_Author;
输出：
['first_name_v', 'last_name_v']
PENELOPE|GUINESS
NICK|WAHLBERG

create view vw_Author
as
    select FirstName 'v_FirstName',LastName 'v_LastName' from Author




4. 描述（10分）
分页查询Employees表，每5行一页，返回第2页的数据

CREATE TABLE Employees
(
   Id int primary key NOT NULL identity,
   EmployeeCode nvarchar(80),
   Birthday date NOT NULL,
   FirstName nvarchar(14) NOT NULL,
   LastName nvarchar(16) NOT NULL,
   Gender char(1) NOT NULL,
   HireDate date NOT NULL
);
插入一些数据：
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10001','1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22');
select * from Employees
查询（5行每页）第2页的记录，输出：
6|10006|1953-04-20|Anneke|Preusig|F|1989-06-02
7|10007|1957-05-23|Tzvetan|Zielinski|F|1989-02-10
8|10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15
9|10009|1952-04-19|Sumant|Peac|F|1985-02-18
10|10010|1963-06-01|Duangkaew|Piveteau|F|1989-08-24


select * from Employees
order by Id
offset 5 rows fetch next 5 rows only






/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2021/10/26 9:36:00                           */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeInfo')
            and   type = 'U')
   drop table EmployeeInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplyForInfo')
            and   type = 'U')
   drop table UserApplyForInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   dep_id               int                  not null,
   dep_name             nvarchar(80)         null,
   manger               nvarchar(80)         null,
   phonenumber          nvarchar(500)        null,
   "e-mail"             nvarchar(500)        null,
   constraint PK_DEPARTMENTINFO primary key ()
)
go

/*==============================================================*/
/* Table: EmployeeInfo                                          */
/*==============================================================*/
create table EmployeeInfo (
   emp_id               int                  null,
   emp_name             nvarchar(80)         null,
   postion              nvarchar(80)         null,
   phonenumber          nvarchar(500)        null,
   salarypackage        money                null,
   accept               nvarchar(80)         null
)
go

/*==============================================================*/
/* Table: UserApplyForInfo                                      */
/*==============================================================*/
create table UserApplyForInfo (
   ap_id                int                  null,
   user_id              int                  null,
   data                 date                 null,
   days                 int                  null,
   peoplecount          int                  null,
   address              nvarchar(100)        null,
   budget               money                null,
   accept               nvarchar(100)        default('')        null,
   constraint AK_KEY_1_USERAPPL unique (ap_id)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   user_id              char(10)             null,
   user_name            char(10)             null,
   bankid               char(10)             null,
   phonenumber          char(10)             null,
   adress               char(10)             null,
   constraint AK_KEY_1_USERINFO unique (user_id)
)
go



CREATE TRIGGER tri_UserInfo
    ON UserApplyForInfo 
    FOR INSERT
    AS
    BEGIN
	declare @accept nvarchar(100)
	if(@accept='')
		begin
		select * from inserted
		end
	else
		begin
		update UserApplyForInfo set accept=''
		end
    END

	go

create proc proc_aa
as
begin

end
